A Concatenated Index is an index involving more than one column.
eg:
CREATE INDEX my_table_i1 ON my_table(key_col1, key_col2, key_col3)
Oracle can use a concatenated index even if you don't provide WHERE clauses on all of the columns, but the columns you do supply must be the leading columns in the index.
For example, consider the following SQLs on the underlying table of the above index:
SELECT * FROM my_table WHERE key_col1 = :a AND key_col2 = :b |
Oracle will scan on the first two columns of the index. |
SELECT * FROM my_table WHERE key_col1 = :a AND key_col3 = :c |
Oracle will scan only one the first column of the index - ie. The leading portion of the index. |
SELECT * FROM my_table WHERE key_col1 = :a AND key_col2 like '%' AND key_col3 = :c |
There are no sneaky workarounds to the previous case. Oracle will still only scan on the first column. |
SELECT * FROM my_table WHERE key_col2 = :b AND key_col3 = :c |
As of v9i, the CBO may use an Index Skip Scan if only the leading column of the index is not included in the predicates. |
SELECT * FROM my_table WHERE key_col3 = :c |
Oracle will not use the index, because the leading columns are not supplied. |
SELECT * FROM my_table WHERE key_col1 = :a AND key_col2 >= :b AND key_col3 = :c |
Oracle will scan on the first column, and range scan on the second column, but will not use the index to scan on the third. The use of a range predicate (>[=], <[=], LIKE, BETWEEN) or an IN list forces that column to be the last one used in the scan. |
Beware the Range Scan trap when using only the leading part of a concatenated index. Consider the following:
CREATE UNIQUE INDEX job_i1
ON job (company_code, department_code, job_id);
SELECT *
FROM job
WHERE company_code = 'US'
AND job_id = 1134;
SELECT STATEMENT
TABLE ACCESS BY ROWID JOB
UNIQUE INDEX RANGE SCAN JOB_I1
We are providing a very selective WHERE clause that will return probably just one row, so we expect the SQL to be fast. Explain Plan tells us that it using the index, so it looks alright. The problem is that we are not providing the department_code in the WHERE clause, so the index can only use the leading column provided - ie. company_code. Assuming company_code is not very selective, we will end up reading most of the index trying to find our one row.
This problem becomes even worse when the range scan is performed on the outer table of a nested loop join, or in a nested sub-query. When this happens, we end up reading the entire index over and over again.